/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.comut.crud.dao;

import com.comut.crud.pojo.FindAndSet;
import com.comut.crud.pojo.user.User;
import com.google.gson.JsonArray;
import com.google.gson.JsonObject;
import com.google.gson.JsonParser;
import com.comut.lib.error.ErrorCodes;
import com.comut.lib.exception.BaseException;
import com.comut.lib.exception.ObjectFoundException;
import com.comut.lib.exception.ObjectNotFoundException;
import java.sql.*;
import java.util.ResourceBundle;
import org.apache.log4j.Logger;

/**
 *
 * @author asantos
 */
public class UserDao {

    private static Logger _logger = Logger.getLogger(UserDao.class.getName());
    private Connection _connection;

    public UserDao(Connection connection) {
        this._connection = connection;
    }

    public ResultSet getUser(int id) {

        ResultSet rs = null;
        CallableStatement stmt = null;
        String query = "{call [crud].[usp_getUser](?)}";
        try {
            stmt = _connection.prepareCall(query);
            stmt.setInt(1, id);
            rs = stmt.executeQuery();
        } catch (SQLException ex) {
            _logger.error("getUser error", ex);
            throw new BaseException(ex, ex.getErrorCode());
        } finally {
        }

        return rs;
    }

    public int createUser(User user) {

        int toRet = -1;
        ResultSet rs = null;
        CallableStatement stmt = null;
        String query = "{call [crud].[usp_createUser](?,?,?,?,?,?,?,?,?)}";
        try {

            stmt = _connection.prepareCall(query);
            stmt.setInt(1, user.getId_sub_group());
            stmt.setInt(2, user.getId_role());
            stmt.setString(3, user.getLogin());
            stmt.setString(4, user.getPassword());
            stmt.setString(5, user.getEmail());
            stmt.setString(6, user.getName());
            stmt.setString(7, user.getActivation());
            stmt.setString(8, user.getExpiration());
            stmt.setString(9, user.getToken());
            rs = stmt.executeQuery();

            if (rs.next()) {
                toRet = rs.getInt(1);
            }
        } catch (SQLException ex) {
            _logger.error("createUser error", ex);
            if (ex.getErrorCode() == ErrorCodes.OBJECT_FOUND) {
                throw new ObjectFoundException(ex, ex.getErrorCode());
            } else {
                throw new BaseException(ex, ex.getErrorCode());
            }
        } catch (Exception ex) {
            _logger.error("createUser error", ex);
            throw new BaseException(ex, ErrorCodes.GENERAL_ERROR);
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException ignore) {
                }
            }
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException ignore) {
                }
            }
        }

        return toRet;
    }

    /**
     * Update the user table in SQLServer
     *
     * @param user Object with the where clause and the update clause
     */
    public void updateUser(FindAndSet user) {
        String userSubgroup = ResourceBundle.getBundle("dbFields").getString("userSubgroup");
        String userLogin = ResourceBundle.getBundle("dbFields").getString("userLogin");

        String update = "UPDATE [auth].[struct_user] SET " + user.getUpdate(); //DELTA = " + delta
        String where = " WHERE " + user.getFind();

        PreparedStatement stmt = null;
        try {


            //check if the new id_sub_group belongs to the same group
            JsonParser jp = new JsonParser();
            JsonArray js = (JsonArray) jp.parse(user.getObject());

            //check if the id_sub_group is defined
            JsonObject updateFind = (JsonObject) js.get(0);
            JsonObject updateObjSet = (JsonObject) ((JsonObject) js.get(1)).get("$set");

            if (updateObjSet.has(userSubgroup)) {
                String getGroupOfTwoSubgroups = "{call [crud].[usp_getGroupOfTwoSubgroups](?,?)}";
                stmt = _connection.prepareCall(getGroupOfTwoSubgroups);
                stmt.setInt(1, updateObjSet.get(userSubgroup).getAsInt());
                stmt.setInt(2, updateFind.get("id").getAsInt());
                ResultSet rs = stmt.executeQuery();

                if (!rs.next()) {
                    throw new BaseException("The new user subgroup does not belong to the same group of the previous subgroup.", ErrorCodes.SUBGROUP_FROM_OTHER_GROUP);
                }
            }


            //check if the new login exists in db
            if (updateObjSet.has(userLogin)) {
                String getGroupOfTwoSubgroups = "{call [crud].[usp_getUniqueLogin](?,?)}";
                stmt = _connection.prepareCall(getGroupOfTwoSubgroups);
                stmt.setInt(1, updateFind.get("id").getAsInt());
                stmt.setString(2, updateObjSet.get(userLogin).getAsString());
                ResultSet rs = stmt.executeQuery();

                if (rs.next()) {
                    throw new BaseException("The new user login already exists.", ErrorCodes.LOGIN_FOUND);
                }
            }

            stmt = _connection.prepareStatement(update + where);
            int updateRows = stmt.executeUpdate();
            if (updateRows == 0) {
                throw new ObjectNotFoundException("The user does not exist", ErrorCodes.OBJECT_NOT_FOUND);
            }

        } catch (SQLException ex) {
            _logger.fatal("updateUser error", ex);
            throw new BaseException(ex, ex.getErrorCode());
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException ignore) {
                }
            }
        }

    }

    public void deleteUser(int id) {
        CallableStatement stmt = null;
        String query = "{call [crud].[usp_deleteUser](?)}";
        try {
            stmt = _connection.prepareCall(query);
            stmt.setInt(1, id);
            stmt.execute();
        } catch (SQLException ex) {
            _logger.error("deleteUser error", ex);
            if (ex.getErrorCode() == ErrorCodes.OBJECT_NOT_FOUND) {
                throw new ObjectFoundException(ex, ex.getErrorCode());
            } else {
                throw new BaseException(ex, ex.getErrorCode());
            }
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException ignore) {
                }
            }
        }
    }

    public ResultSet getUserTags(int userId) {

        ResultSet rs = null;
        CallableStatement stmt = null;
        String query = "{call [crud].[usp_getUserTags](?)}";
        try {
            stmt = _connection.prepareCall(query);
            stmt.setInt(1, userId);
            rs = stmt.executeQuery();
        } catch (SQLException ex) {
            _logger.error("getUserTags error", ex);
            throw new BaseException(ex, ex.getErrorCode());
        } finally {
        }

        return rs;
    }

    /**
	 * @author Paulo Fournier
     * Issue: IFL-1565
     * Data access object to check the user's authentication
     * @param login The user's login
     * @param password The user's password
     * @return ResultSet the database query result set
     */
    public ResultSet checkAuthentication(String login, String password) {
        ResultSet resultSet = null;
        CallableStatement statement = null;
        String query = "{call [auth].[usp_checkAuthentication](?,?)}";
        
        try {
            statement = _connection.prepareCall(query);
            statement.setString(1, login);
            statement.setString(2, password);
            resultSet = statement.executeQuery();
        } catch (SQLException ex) {
            _logger.error("checkAuthentication error", ex);
            throw new BaseException(ex, ex.getErrorCode());
        } finally {
        }

        return resultSet;
    }
}
